package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.jhc.bean.Title;
import cn.jhc.bean.User;
import cn.jhc.factory.ConnectionFactory;

/**
 * Title Data Access Object
 * 
 * @author luyanfei
 * 
 */
public class TitleDAO {
	
	public int getRowsCount() {
		
		int rowscount = 0;
		Connection connection=ConnectionFactory.getConnection();
		final String sql="select count(*) from titles ";

		Statement statement=null;
		ResultSet rs=null;
		
		try {
			statement =(Statement) connection.createStatement();
			rs=statement.executeQuery(sql);
			while(rs.next()){
				rowscount =rs.getInt(1);
			
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				rs.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rowscount;
	}

	public List<Title> getTitlesInPage(int page, int pageNum){
		
        List<Title> list =new ArrayList<>();
		
		Connection connection=ConnectionFactory.getConnection();
		final String sql="select isbn,title ,editionNumber,copyright,publisherID,imageFile," +
				" price from titles limit ?,?";
		
		ResultSet rs=null;
		PreparedStatement ps = null;
		
		try {
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, pageNum);
			rs = ps.executeQuery();
			while(rs.next()){
				Title title = new Title();
				title.setIsbn(rs.getString(1));
				title.setTitle(rs.getString(2));
				title.setEditionNumber(rs.getInt(3));
				title.setCopyright(rs.getString(4));
				title.setPublisherId(rs.getInt(5));
				title.setImageFile(rs.getString(6));
				title.setPrice(rs.getDouble(7));
				list.add(title);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				rs.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		}
		return list;
	}
	
	public List<Title> getAll() {
		List<Title> list = new ArrayList<>();
		Connection connection = ConnectionFactory.getConnection();
		final String sql = "select isbn,title,editionNumber,copyright,publisherID,"
				+ "imageFile,price from titles";
		Statement statement = null;
		ResultSet rs = null;

		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Title title = new Title();
				title.setIsbn(rs.getString(1));
				title.setTitle(rs.getString(2));
				title.setEditionNumber(rs.getInt(3));
				title.setCopyright(rs.getString(4));
				title.setPublisherId(rs.getInt(5));
				title.setImageFile(rs.getString(6));
				title.setPrice(rs.getDouble(7));
				list.add(title);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public Title findByIsbn(String isbn) {
		Title title = new Title();
		Connection connection = ConnectionFactory.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		final String sql = "select isbn,title,editionNumber,copyright,publisherID,"
				+ "imageFile,price from titles where isbn = ?";
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, isbn);
			rs = ps.executeQuery();
			if(rs.next()) {
				title.setIsbn(rs.getString(1));
				title.setTitle(rs.getString(2));
				title.setEditionNumber(rs.getInt(3));
				title.setCopyright(rs.getString(4));
				title.setPublisherId(rs.getInt(5));
				title.setImageFile(rs.getString(6));
				title.setPrice(rs.getDouble(7));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				ps.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return title;
	}

	
	public boolean addBooks(Title titles){
		Statement stat = null;
		boolean action = true;
		Connection conn = (Connection) ConnectionFactory.getConnection();
		
		String sql = "insert into titles(isbn,title,editionNumber,copyright,publisherId,imageFile,price) "
				+ "values('"+titles.getIsbn()+"','"+ titles.getTitle()+"',"+
		titles.getEditionNumber()+",'"+titles.getCopyright()+"',"+titles.getPublisherId()+",'"+titles.getImageFile()+
		"',"+titles.getPrice()+")";
		
		try
		{
			stat = (Statement) conn.createStatement();
			stat.execute(sql);
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try
			{
				stat.close();
				conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return action;
	}
}
